package com.example.demo;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;

import java.math.BigDecimal;
import java.util.*;
import java.util.stream.Collectors;

/**
 * @author zhangqiang
 * @date 2021/6/27 21:38
 */
public class BusinessUtil {

    /**
     * 从数据中获取给定横纵标对应值
     *
     * @param wageStandardMap                 标准表设置信息
     * @param wageStandardGradeMapList        标准表数据映射信息
     * @param personSysInfoItemIdValueMapList 人员信息列表
     * @return map key:personId人员唯一映射信息-value结果值
     * <p>
     * 1.wageStandardMap 标准表设置信息--------------------------------------------
     * key:
     * abscissa 横坐标;                            [例:{"abscissa":"A001339,A440203"}]
     * ordinate 纵坐标;                            [例:{"ordinate":"A001204,A001336"}]
     * abscissaSysInfoSetId 横坐标对应信息集;      [例:{"abscissaSysInfoSetId":"A001,A440"}]
     * ordinateSysInfoSetId 纵坐标对应信息集;      [例:{"ordinateSysInfoSetId":"A001,A440"}]
     * <p>
     * 2.wageStandardGradeMapList 标准表数据映射信息-------------------------------
     * key:
     * abscissa 横坐标;                           [例:{"abscissa":"3173000008,3173000015"}]
     * ordinate 纵坐标;                           [例:{"ordinate":"3173000008,3173000015"}]
     * amount 数值;                               [例:{"amount":"15000"}]
     * <p>
     * 3.personSysInfoItemIdValueMap 人员信息列表---------------------------------
     * key:
     * coordinate 各个坐标字段与对应值;            [例:{"A001339":"一等二级","A440203":"1.2","personId":"888888888"}]
     * personId (人员唯一映射信息)
     */
    public static Map<String, String> multidimensionalDataMapping(Map<String, String> wageStandardMap, List<Map<String, String>> wageStandardGradeMapList, List<Map<String, String>> personSysInfoItemIdValueMapList) {
        // 获取标准表设置
        List<String> wageStandardAbscissaSysInfoItemIdList = Arrays.asList(wageStandardMap.get("ABSCISSA").split(","));
        List<String> wageStandardOrdinateSysInfoItemIdList = Arrays.asList(wageStandardMap.get("ORDINATE").split(","));
        // 解析wageStandardGrade
        List<Map<String, String>> wageStandardGradeMapListWithAnalyzed = new ArrayList<>();
        wageStandardGradeMapList.forEach(item -> {
            List<String> wageStandardGradeAbscissaValueList = Arrays.stream(item.get("ABSCISSA")
                    .split(",")).filter(StringUtils::isNotBlank).collect(Collectors.toList());
            List<String> wageStandardGradeOrdinateValueList = Arrays.stream(item.get("ORDINATE")
                    .split(",")).filter(StringUtils::isNotBlank).collect(Collectors.toList());
            wageStandardGradeAbscissaValueList.sort(Comparator.comparing(String::hashCode));
            wageStandardGradeOrdinateValueList.sort(Comparator.comparing(String::hashCode));
            Map<String, String> newMap = new HashMap<>(3);
            newMap.put("ABSCISSA", wageStandardGradeAbscissaValueList.toString());
            newMap.put("ORDINATE", wageStandardGradeOrdinateValueList.toString());
            newMap.put("AMOUNT", item.get("AMOUNT"));
            wageStandardGradeMapListWithAnalyzed.add(newMap);
        });
        wageStandardGradeMapList.clear();
        // 数值获取
        Map<String, String> resultMap = new HashMap<>(personSysInfoItemIdValueMapList.size());
        personSysInfoItemIdValueMapList.forEach(data -> {
            List<String> personAbscissaValueList = new ArrayList<>();
            wageStandardAbscissaSysInfoItemIdList.forEach(itemId -> {
                if (StringUtils.isNotBlank(itemId) && StringUtils.isNotBlank(data.get(itemId))) {
                    personAbscissaValueList.add(data.get(itemId));
                }
            });
            List<String> personOrdinateValueList = new ArrayList<>();
            wageStandardOrdinateSysInfoItemIdList.forEach(itemId -> {
                if (StringUtils.isNotBlank(itemId) && StringUtils.isNotBlank(data.get(itemId))) {
                    personOrdinateValueList.add(data.get(itemId));
                }
            });
            personAbscissaValueList.sort(Comparator.comparing(String::hashCode));
            personOrdinateValueList.sort(Comparator.comparing(String::hashCode));
            Map<String, String> valueMap = wageStandardGradeMapListWithAnalyzed.stream().filter(item -> item.get("ABSCISSA").equals(personAbscissaValueList.toString()) && item.get("ORDINATE").equals(personOrdinateValueList.toString())
            ).findFirst().orElse(null);
            resultMap.put(data.get("PERSON_ID"), valueMap == null ? "0" : valueMap.get("AMOUNT"));
        });
        return resultMap;
    }

    /**
     * 获取Excel单元格值
     */
    public static String getStringCellValue(Cell cell){
        if(cell == null){
            return null;
        }
        String cellValue;
        switch (cell.getCellType()){
            case BOOLEAN:
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case NUMERIC:
                if(DateUtil.isCellDateFormatted(cell)){
                    cellValue = DateFormatUtils.format(cell.getDateCellValue(),"yyyy-MM-dd HH:ss:mm");
                } else {
                    cellValue = new BigDecimal(String.valueOf(cell.getNumericCellValue())).stripTrailingZeros().toPlainString();
                }
                break;
            case STRING:
                cellValue = cell.getStringCellValue();
                break;
            case FORMULA:
                cellValue = cell.getCellFormula();
                break;
            default:
                cellValue = cell.toString();
        }
        return cellValue.trim();
    }
}
